package com.freemalll.merchant.service.impl;


import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.freemalll.common.core.enums.BaseResponseCodeEnum;
import com.freemalll.common.core.exception.BusinessException;
import com.freemalll.common.redis.service.RedisService;
import com.freemalll.merchant.constant.RedisKeyConstant;
import com.freemalll.merchant.domain.dto.merchantParameters.*;
import com.freemalll.merchant.domain.entity.merchantParameters.MerParams;
import com.freemalll.merchant.domain.entity.merchantParameters.MerParamsDetail;
import com.freemalll.merchant.domain.entity.merchantParameters.MerParamsLog;
import com.freemalll.merchant.domain.vo.merchantParameters.MerParamsDetailVO;
import com.freemalll.merchant.domain.vo.merchantParameters.MerParamsListVO;
import com.freemalll.merchant.enums.ParamsLogType;
import com.freemalll.merchant.mapper.MerParamsDetailMapper;
import com.freemalll.merchant.mapper.MerParamsLogMapper;
import com.freemalll.merchant.mapper.MerParamsMapper;
import com.freemalll.merchant.service.MerParamsService;
import com.freemalll.merchant.utils.AliOSSUtil;
import com.freemalll.merchant.utils.BeanUtils;
import com.freemalll.merchant.utils.ExcelToTreeParserUtil;
import com.freemalll.merchant.utils.RedisUtil;
import jakarta.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 服务层实现。
 *
 * @author mybatis-flex-helper automatic generation
 * @since 1.0
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class MerParamsServiceImpl extends ServiceImpl<MerParamsMapper, MerParams> implements MerParamsService {

    private final MerParamsMapper merParamsMapper;
    private final MerParamsDetailMapper merParamsDetailMapper;
    private final RedisUtil redisUtil;
    private final RedisService redisService;
    private final AliOSSUtil aliOSSUtil;
    private final MerParamsLogMapper merParamsLogMapper;
    private final ExcelToTreeParserUtil excelToTreeParserUtil;
    @Value("classpath:/download/mer_param_import_template.xlsx")
    private Resource paramTemple;
    private static final String[] CHINESE_NUMS =
            {"零", "一", "二", "三", "四", "五", "六", "七", "八", "九"};

    private final String PARAM_EXPORT_PATH = "param/export";

    private final String PARAM_IMPORT_PATH = "param/import";
    // 匹配"参数|值"或单个"参数"的正则表达式
    private static final Pattern PARAM_PATTERN = Pattern.compile("([^|,]+)(?:\\|([^,]+))?");

    @Override
    public MerParamsDetailVO detail(String key) {
        MerParams paramsEntity = merParamsMapper.selectOne(Wrappers.<MerParams>lambdaQuery().eq(MerParams::getParamCode, key).or().eq(MerParams::getId, key));
        if (Objects.isNull(paramsEntity) || StringUtils.isBlank(paramsEntity.getId())) {
            throw new BusinessException(BaseResponseCodeEnum.CODE_200, "参数不存在");
        }
        MerParamsDetailVO merParamsDetailVO = new MerParamsDetailVO();
        BeanUtils.copyPropertiesIgnoreNull(paramsEntity, merParamsDetailVO);
        //查询所有节点
        List<MerParamsDetailDTO> merParamsDetails = merParamsDetailMapper.selectListByDetailsId(paramsEntity.getId());
        if (CollectionUtils.isNotEmpty(merParamsDetails)) {
            List<MerParamsDetailDTO> merParamsDetailDTOS = buildTree(merParamsDetails, null);
            merParamsDetailVO.setParamsDetailList(merParamsDetailDTOS);
        }
        //查询日志
        List<ParamsLogDTO> paramsLogDTOS = merParamsLogMapper.selectListByParentId(paramsEntity.getId());
        merParamsDetailVO.setParamsLogList(paramsLogDTOS);
        //查询应用的板块
        if (StringUtils.isNotBlank(paramsEntity.getApplyLocation())) {
            List<String> detailIds = Arrays.asList(paramsEntity.getApplyLocation().split(","));
            List<MerParamsDetailDTO> applyLocationList = merParamsDetailMapper.selectListByMerParamsId(detailIds);
            merParamsDetailVO.setApplyLocationList(applyLocationList);
        }
        return merParamsDetailVO;
    }

    @Override
    public MerParamsDetailVO cacheDetail(String paramId) {
        String paramObj = (String) redisUtil.get(RedisKeyConstant.MER_PARAM + paramId);
        if (ObjectUtil.isNotNull(paramObj)) {
            return JSON.parseObject(paramObj, MerParamsDetailVO.class);
        }
        MerParamsDetailVO detail = this.detail(paramId);
        redisUtil.set(RedisKeyConstant.MER_PARAM + paramId, JSON.toJSONString(detail), 7, TimeUnit.DAYS);
        return detail;
    }

    /**
     * 构建树
     */
    private List<MerParamsDetailDTO> buildTree(List<MerParamsDetailDTO> paramsDetailList, String parentId) {
        return paramsDetailList.stream()
                // 过滤出当前父节点的直接子节点
                .filter(e -> Objects.equals(e.getParentId(), parentId))
                // 按orderNo倒序排序，处理null值
                .sorted((e1, e2) -> {
                    Integer o1 = e1.getOrderNo();
                    Integer o2 = e2.getOrderNo();
                    // 处理null值，将null视为小于任何数值（倒序时放在最后）
                    if (o1 == null && o2 == null) return 0;
                    if (o1 == null) return 1;  // null放后面
                    if (o2 == null) return -1; // 有值的放前面
                    return o2.compareTo(o1);   // 倒序比较（用o2比o1）
                })
                // 递归构建子节点
                .peek(e -> e.setChildren(buildTree(paramsDetailList, e.getId())))
                .collect(Collectors.toList());
    }



    @Override
    @Transactional(rollbackFor = Exception.class)
    public String saveOrUpdate(DTOMerParams merParams) {
        if (StringUtils.isBlank(merParams.getId())) {
            //根据模版编码查询是否存在
            MerParams paramsEntity = merParamsMapper.selectOne(Wrappers.<MerParams>lambdaQuery().eq(MerParams::getParamCode, merParams.getParamCode()).last(" limit 1 "));
            if (Objects.nonNull(paramsEntity) && !paramsEntity.getId().equals(merParams.getId())) {
                throw new BusinessException(BaseResponseCodeEnum.CODE_10010, "参数模板编码重复");
            }
        }
        //校验key不能重复
        MerParams paramsEntity = merParamsMapper.selectOne(Wrappers.<MerParams>lambdaQuery().eq(MerParams::getParamName, merParams.getParamName()).last(" limit 1 "));
        if (Objects.nonNull(paramsEntity) && !paramsEntity.getId().equals(merParams.getId())) {
            throw new BusinessException(BaseResponseCodeEnum.CODE_10011, "参数模板名称重复");
        }
        MerParams merParamsEntity = new MerParams();
        String id;
        if (StringUtils.isNotBlank(merParams.getId())) {
            //更新
            merParamsEntity = merParamsMapper.selectById(merParams.getId());
            BeanUtils.copyPropertiesIgnoreNullAndEmptyStr(merParams, merParamsEntity);
            merParamsEntity.initUpdate();
            merParamsMapper.updateById(merParamsEntity);
            id = merParams.getId();
            //删除详情
            List<String> collect = extractAllIds(merParams.getParamsDetailList());
            if (CollectionUtils.isEmpty(collect)) {
                merParamsDetailMapper.delete(Wrappers.<MerParamsDetail>lambdaQuery().eq(MerParamsDetail::getMerParamsId, merParams.getId()));
            } else {
                //剔除不删的
                merParamsDetailMapper.delete(Wrappers.<MerParamsDetail>lambdaQuery().eq(MerParamsDetail::getMerParamsId, merParams.getId()).notIn(MerParamsDetail::getId, collect));
            }
        } else {
            //新增
            BeanUtils.copyPropertiesIgnoreNullAndEmptyStr(merParams, merParamsEntity);
            merParamsMapper.insert(merParamsEntity);
            id = merParamsEntity.getId();
        }
        List<MerParamsDetailDTO> paramsDetaiList = merParams.getParamsDetailList();
        if (CollectionUtils.isNotEmpty(paramsDetaiList)) {
            for (int i = 0; i < paramsDetaiList.size(); i++) {
                MerParamsDetailDTO merParamsDetailDTO = paramsDetaiList.get(i);
                merParamsDetailDTO.setOrderNo(i + 1);
                saveTree(merParamsDetailDTO, null, id, 1);
            }
        }
        //插入日志
        if (Objects.nonNull(merParams.getExcelParsingDto())) {
            //记录导入日志
            MerParamsLog merParamsLog = new MerParamsLog();
            merParamsLog.setContent(merParams.getExcelParsingDto().getFilePath());
            merParamsLog.setLogType(ParamsLogType.IMPORT.getCode());
            merParamsLog.setFileName(merParams.getExcelParsingDto().getFileName());
            merParamsLog.setParamId(id);
            merParamsLogMapper.insert(merParamsLog);
        }
        //查询更新缓存
        MerParamsDetailVO detail = this.detail(id);
        redisUtil.set(RedisKeyConstant.MER_PARAM + merParams.getParamCode(), JSON.toJSONString(detail), 7, TimeUnit.DAYS);
        return id;
    }

    public List<String> extractAllIds(List<MerParamsDetailDTO> nodes) {
        return nodes.stream().filter(Objects::nonNull)
                .flatMap(node -> {
                    List<String> result = new ArrayList<>();
                    if (node.getId() != null && !node.getId().isEmpty()) {
                        result.add(node.getId());
                    }
                    result.addAll(extractAllIds(node.getChildren()));
                    return result.stream();
                })
                .collect(Collectors.toList());
    }

    /**
     * @param node        当前节点
     * @param parent      父节点
     * @param merParamsId 参数模板id
     * @param level       层级
     */
    @Transactional
    public void saveTree(MerParamsDetailDTO node, MerParamsDetailDTO parent, String merParamsId, int level) {
        if (parent != null) {
            node.setParentId(parent.getId());  // 设置父节点 ID
        }
        MerParamsDetail merParamsDetail = new MerParamsDetail();
        BeanUtils.copyPropertiesIgnoreNull(node, merParamsDetail);
        merParamsDetail.setMerParamsId(merParamsId);
        merParamsDetail.setLevelNo(level);
        if (StringUtils.isNotBlank(merParamsDetail.getId())) {
            List<MerParamsDetail> merParamsDetails = merParamsDetailMapper.selectList(Wrappers.<MerParamsDetail>lambdaQuery()
                    .eq(MerParamsDetail::getMerParamsId, merParamsId)
                    .notIn(MerParamsDetail::getId, merParamsDetail.getId())
                    .eq(MerParamsDetail::getParamKey, merParamsDetail.getParamKey()));
            if (CollectionUtils.isNotEmpty(merParamsDetails)) {
                throw new BusinessException(BaseResponseCodeEnum.CODE_10012, "参数模板key重复");
            }
            merParamsDetailMapper.updateById(merParamsDetail);// 保存当前节点
        } else {
            List<MerParamsDetail> merParamsDetails1 = merParamsDetailMapper.selectList(Wrappers.<MerParamsDetail>lambdaQuery()
                    .eq(MerParamsDetail::getMerParamsId, merParamsId)
                    .eq(MerParamsDetail::getParamKey, merParamsDetail.getParamKey()));
            if (CollectionUtils.isNotEmpty(merParamsDetails1)) {
                throw new BusinessException(BaseResponseCodeEnum.CODE_10012, "参数模板key重复");
            }
            merParamsDetailMapper.insert(merParamsDetail);
        }

        node.setId(merParamsDetail.getId());
        // 递归保存子节点,层级,节点间相对顺序
        List<MerParamsDetailDTO> children = node.getChildren();
        if (CollectionUtils.isNotEmpty(children)) {
            for (int i = 0; i < children.size(); i++) {
                MerParamsDetailDTO merParamsDetailDTO = children.get(i);
                merParamsDetailDTO.setOrderNo(i + 1);
                saveTree(merParamsDetailDTO, node, merParamsId, level + 1);
            }
        }
    }


    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteById(String id) {
        MerParams merParamsEntity = merParamsMapper.selectById(id);
        if (Objects.isNull(merParamsEntity)) {
            throw BusinessException.businessDataNotExist("模板不存在");
        }
        merParamsMapper.deleteById(merParamsEntity.getId());
        redisService.deleteObject(RedisKeyConstant.MER_PARAM + id);
    }

    @Override
    public Page<MerParamsListVO> paramsPageList(ParamQueryDTO paramQueryDTO) {
        Page<MerParamsListVO> merParamsListVOPage = merParamsMapper.pageSearch(paramQueryDTO.page(), paramQueryDTO);
        for (MerParamsListVO record : merParamsListVOPage.getRecords()) {
            String applyLocation = record.getApplyLocation();
            //查询应用的板块
            if (StringUtils.isNotBlank(applyLocation)) {
                List<String> detailIds = StrUtil.split(applyLocation, ",");
                List<MerParamsDetailDTO> applyLocationList = merParamsDetailMapper.selectListByMerParamsId(detailIds);
                record.setApplyLocationList(applyLocationList);
            }
        }
        return merParamsListVOPage;
    }

    @Override
    public void templateDownload() {
        try {
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            InputStream is = paramTemple.getInputStream();
            Workbook wk = WorkbookFactory.create(is);
            wk.write(baos);
            String file = Base64.getEncoder().encodeToString(baos.toByteArray());
            if (StringUtils.isNotBlank(file)) {
                HttpServletResponse response = ((ServletRequestAttributes) Objects.requireNonNull(RequestContextHolder.getRequestAttributes())).getResponse();
                String fileName = URLEncoder.encode("参数导入模板.xls", StandardCharsets.UTF_8.name());
                response.setHeader("Content-Disposition", String.format("attachment;filename=\"%s\";filename*=\"utf-8''%s\"", fileName, fileName));
                response.getOutputStream().write(Base64.getDecoder().decode(file));
            }
        } catch (Exception e) {
            log.error("下载参数模板出错", e);
            throw new RuntimeException("参数模板下载失败", e.getCause());
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void exportParam(String paramId) {
        MerParams paramsEntity = merParamsMapper.selectById(paramId);
        //查询所有节点
        List<MerParamsDetailDTO> merParamsDetailList = merParamsDetailMapper.selectListByDetailsId(paramId);
        MerParamsDetailVO detail = this.detail(paramId);
        if (Objects.nonNull(detail) && CollectionUtils.isNotEmpty(detail.getParamsDetailList())) {
            List<List<ParamsExportDTO>> result = new ArrayList<>();
            // 找出所有根节点
            List<MerParamsDetailDTO> rootNodes = merParamsDetailList.stream()
                    .filter(node -> node.getParentId() == null || node.getParentId().equals("0"))
                    .collect(Collectors.toList());
            for (MerParamsDetailDTO root : rootNodes) {
                buildExcelRows(root, merParamsDetailList, new ArrayList<>(), result);
            }
            //查找最大的层级数
            int level = 1;
            Optional<MerParamsDetailDTO> maxLevel = merParamsDetailList.stream().max(Comparator.comparing(MerParamsDetailDTO::getLevelNo));
            if (maxLevel.isPresent()) {
                level = maxLevel.get().getLevelNo();
            }
            LocalDateTime dateTime = LocalDateTime.now();
            String fileName = String.format(paramsEntity.getParamName() + "_参数导出_%s.xls", dateTime.format(DateTimeFormatter.ofPattern("yyyyMMddHHmm")) + RandomUtil.randomNumbers(4));
            // 创建 Excel 工作簿
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("参数详情");
            CellStyle headerStyle = workbook.createCellStyle();
            Font headerFont = workbook.createFont();
            headerFont.setBold(true); // 加粗
            headerStyle.setFont(headerFont);
            headerStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
            headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
            //第 0 行作为表头
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < level; i++) {
                String chineseNum = CHINESE_NUMS[i + 1];
                Cell codeCell = headerRow.createCell(2 * i);
                codeCell.setCellValue(chineseNum + "级参数编码"); // 设置表头内容
                codeCell.setCellStyle(headerStyle);
                Cell nameCell = headerRow.createCell(2 * i + 1);
                nameCell.setCellValue(chineseNum + "级参数名称"); // 设置表头内容
                nameCell.setCellStyle(headerStyle);
                sheet.setColumnWidth(2 * i, 4000);  // 统一列宽 4000
                sheet.setColumnWidth(2 * i + 1, 4000); // 统一列宽 4000
            }
            //两列一组处理单元格数据
            for (int i = 0; i < result.size(); i++) {
                Row row = sheet.createRow(i + 1); // 数据行从第 1 行开始
                List<ParamsExportDTO> paramsExportDTOS = result.get(i);
                for (int j = 0; j < paramsExportDTOS.size(); j++) {
                    ParamsExportDTO paramsExportDTO = paramsExportDTOS.get(j);
                    row.createCell(2 * j).setCellValue(paramsExportDTO.getParamKey());
                    row.createCell(2 * j + 1).setCellValue(paramsExportDTO.getParamName());
                }
            }
            try {
                //上传到oss
                ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
                workbook.write(outputStream);
                workbook.close();
                String filePath = aliOSSUtil.uploadFile(new ByteArrayInputStream(outputStream.toByteArray()), PARAM_EXPORT_PATH, fileName);
                String fileUrl = aliOSSUtil.getFileUrl(filePath);
                log.info("上传到oss的文件访问地址:{}", fileUrl);
                HttpServletResponse response = ((ServletRequestAttributes) Objects.requireNonNull(RequestContextHolder.getRequestAttributes())).getResponse();
                // 设置响应头
                String encodeFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.setHeader("Content-Disposition", String.format("attachment;filename=\"%s\";filename*=\"utf-8''%s\"", encodeFileName, encodeFileName));
                outputStream.writeTo(response.getOutputStream());
                outputStream.flush();
                outputStream.close();
                //记录日志
                MerParamsLog merParamsLog = new MerParamsLog();
                merParamsLog.setContent(fileUrl);
                merParamsLog.setLogType(ParamsLogType.EXPORT.getCode());
                merParamsLog.setFileName(fileName);
                merParamsLog.setParamId(paramId);
                merParamsLogMapper.insert(merParamsLog);
            } catch (IOException e) {
                log.error("导出参数失败", e);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 递归构造 Excel 行数据，并保证层级顺序
     *
     * @param node                   当前节点
     * @param merParamsDetailDTOList 所有节点集合
     * @param currentRow             当前行数据
     * @param result                 最后的结果集
     */
    private void buildExcelRows(MerParamsDetailDTO node, List<MerParamsDetailDTO> merParamsDetailDTOList,
                                List<ParamsExportDTO> currentRow, List<List<ParamsExportDTO>> result) {
        //当前行数据
        List<ParamsExportDTO> newRow = new ArrayList<>(currentRow);
        ParamsExportDTO paramsExportDTO = ParamsExportDTO
                .builder()
                .levelNo(node.getLevelNo())
                .paramKey(node.getParamKey())
                .paramName(node.getParamName()).build();
        newRow.add(paramsExportDTO);
        // 找到当前节点的所有子节点，按 order 排序
        List<MerParamsDetailDTO> children = merParamsDetailDTOList.stream()
                .filter(n -> Objects.equals(n.getParentId(), node.getId()))
//                .sorted(Comparator.comparingInt(MerParamsDetailDTO::getOrder)) // 确保子节点按 order 排序
                .collect(Collectors.toList());
        if (children.isEmpty()) {
            result.add(newRow); // 叶子节点，添加到结果集
        } else {
            for (MerParamsDetailDTO child : children) {
                buildExcelRows(child, merParamsDetailDTOList, newRow, result);
            }
        }
    }


    @Override
    public ExcelParsingDto importParam(MultipartFile file) {
        if (file == null || file.isEmpty() || Objects.isNull(file.getOriginalFilename())) {
            throw new BusinessException(BaseResponseCodeEnum.CODE_10011, "导入文件为空!");
        }
        String fileName = file.getOriginalFilename();
        if (fileName == null || (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx"))) {
            throw new BusinessException(BaseResponseCodeEnum.CODE_10011, "请上传Excel格式的文件(.xls或.xlsx)!");
        }
        try {
            List<ImportParamDto> importParamDtos = excelToTreeParserUtil.parseExcelToTree(file);
            String filePath = aliOSSUtil.uploadFile(file.getInputStream(), PARAM_IMPORT_PATH, file.getOriginalFilename());
            String fileUrl = aliOSSUtil.getFileUrl(filePath);
            log.info("导入文件上传到oss的文件访问地址:{}", fileUrl);
            ExcelParsingDto excelParsingDto = new ExcelParsingDto();
            excelParsingDto.setFilePath(fileUrl);
            excelParsingDto.setFileName(file.getOriginalFilename());
            excelParsingDto.setParamsDetailList(importParamDtos);
            return excelParsingDto;
        } catch (IOException e) {
            throw BusinessException.businessOther("解析文件错误，请检查文件格式是否正确!");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    @Override
    public String getParamNameByParamKey(List<MerParamsDetailDTO> paramsDetailList, String paramKey) {
        return this.findParamNameByParamKey(paramsDetailList, paramKey);
    }

    @Override
    public List<MerParamsDetailDTO> getParams(String paramCode, String firstLevelCoding) {
        MerParams paramsEntity = merParamsMapper.selectOne(Wrappers.<MerParams>lambdaQuery().eq(MerParams::getParamCode, paramCode));
        if (Objects.isNull(paramsEntity)) {
            throw new BusinessException(BaseResponseCodeEnum.CODE_200, "参数不存在");
        }
        if (StringUtils.isBlank(firstLevelCoding)) {
            List<MerParamsDetailDTO> merParamsDetails = merParamsDetailMapper.selectListByDetailsId(paramsEntity.getId());
            if (CollectionUtils.isNotEmpty(merParamsDetails)) {
                return buildTree(merParamsDetails, null);
            }
            return Collections.emptyList();
        } else {
            MerParamsDetail merParamsDetail = merParamsDetailMapper.selectOne(Wrappers.<MerParamsDetail>lambdaQuery()
                    .eq(MerParamsDetail::getMerParamsId, paramsEntity.getId())
                    .eq(MerParamsDetail::getParamKey, firstLevelCoding));
            if (Objects.isNull(merParamsDetail)) {
                throw BusinessException.businessDataNotExist("参数编码不存在!");
            }
            List<MerParamsDetailDTO> detailDTOS = merParamsDetailMapper.selectListByParams(paramsEntity.getId(), merParamsDetail.getId());
            List<String> parentIds = detailDTOS.stream().map(MerParamsDetailDTO::getId).collect(Collectors.toList());
            List<MerParamsDetailDTO> merParamsDetailDTOS = merParamsDetailMapper.selectListByParentId(parentIds);
            detailDTOS.addAll(merParamsDetailDTOS);
            return buildTree(detailDTOS, merParamsDetail.getId());
        }
    }

    @Override
    public String translateParamKeys(String paramString) {
        if (paramString == null || paramString.trim().isEmpty()) {
            return "";
        }

        // 1. 先查缓存
        String cacheKey = "param_translate:" + paramString;
        Object cached = redisUtil.get(cacheKey);
        if (cached != null) {
            return cached.toString();
        }

        // 用于存储所有需要查询的参数键
        Set<String> paramKeys = new HashSet<>();

        // 解析输入字符串
        Matcher matcher = PARAM_PATTERN.matcher(paramString);
        while (matcher.find()) {
            paramKeys.add(matcher.group(1)); // 添加主参数键
            if (matcher.group(2) != null) {
                paramKeys.add(matcher.group(2)); // 添加可能存在的子参数键
            }
        }

        // 批量查询所有参数键对应的名称
        Map<String, String> keyToNameMap = fetchParamNameMap(paramKeys);

        // 构建结果字符串
        StringBuilder result = new StringBuilder();
        matcher.reset();

        while (matcher.find()) {
            String mainKey = matcher.group(1);
            String subKey = matcher.group(2);

            // 获取翻译后的主参数名称
            String translatedMainKey = keyToNameMap.getOrDefault(mainKey, mainKey);

            if (subKey != null) {
                // 获取翻译后的子参数名称
                String translatedSubKey = keyToNameMap.getOrDefault(subKey, subKey);
                result.append(translatedMainKey).append("|").append(translatedSubKey).append(",");
            } else {
                result.append(translatedMainKey).append(",");
            }
        }

        // 移除最后的逗号（如果有）
        if (result.length() > 0) {
            result.deleteCharAt(result.length() - 1);
        }

        String translated = result.toString();
        // 2. 写入缓存
        redisUtil.set(cacheKey, translated, 60L * 60L); // 缓存1小时

        return translated;
    }

    @Override
    public String findParamNameByPrefix(String input, List<MerParamsDetailDTO> paramsDetailList) {
        // 将输入字符串按逗号分割
        String[] pairs = input.split(",");
        // 默认只取第一个
        String pair = pairs[0];
        //处理医美场景的 JY03|JY031,JY04|JY041 和大健康场景的 DJK01 只取 JY03或DJK01并去重
        Set<String> prefixes = new HashSet<>();
        //判断是否存在|
        if (pair.contains("|")) {
            // 提取 | 前的前缀部分
            String prefix = pair.split("\\|")[0];
            prefixes.add(prefix);
        } else {
            prefixes.add(pair);
        }
        StringBuilder builder = new StringBuilder();
        for (String prefix : prefixes) {
            String paramNameByParamKey = this.getParamNameByParamKey(paramsDetailList, prefix);
            if (paramNameByParamKey != null) {
                builder.append(paramNameByParamKey).append(",");
            }
        }
        // 删除最后一个多余的逗号（如果存在）
        if (builder.length() > 0 && builder.charAt(builder.length() - 1) == ',') {
            builder.deleteCharAt(builder.length() - 1);
        }
        return builder.toString();
    }

    @Override
    public void checkAndAddBrand(String brandName) {
        Boolean b = baseMapper.existParamName("brand_library",brandName);
        if(Boolean.TRUE.equals(b)) {
            return;
        }
        MerParams merParams = baseMapper.selectOne(Wrappers.<MerParams>lambdaQuery().eq(MerParams::getParamCode, "brand_library"));
        MerParamsDetail detail = new MerParamsDetail();
        detail.setMerParamsId(merParams.getId());
        detail.setParamKey(brandName);
        detail.setParamName(brandName);
        detail.setLevelNo(1);
        merParamsDetailMapper.insert(detail);
        redisUtil.remove(RedisKeyConstant.MER_PARAM + merParams.getParamCode());
    }

    private Map<String, String> fetchParamNameMap(Set<String> paramKeys) {
        if (paramKeys.isEmpty()) {
            return Collections.emptyMap();
        }

        List<MerParamsDetail> details = merParamsDetailMapper.selectList(Wrappers.<MerParamsDetail>lambdaQuery().in(MerParamsDetail::getParamKey, paramKeys));


        Map<String, String> map = new HashMap<>();
        for (MerParamsDetail detail : details) {
            map.put(detail.getParamKey(), detail.getParamName());
        }

        return map;
    }

    public String findParamNameByParamKey(List<MerParamsDetailDTO> paramsDetailList, String targetParamKey) {
        for (MerParamsDetailDTO detail : paramsDetailList) {
            // 检查一级节点
            if (targetParamKey.equals(detail.getParamKey())) {
                return detail.getParamName();
            }
            // 如果有子节点，递归检查
            if (detail.getChildren() != null && !detail.getChildren().isEmpty()) {
                String result = findParamNameByParamKey(detail.getChildren(), targetParamKey);
                if (result != null) {
                    return result;
                }
            }
        }
        return null; // 没有找到匹配项
    }

    /**
     * 处理行数据
     *
     * @param row        行数据
     * @param level      层级
     * @param parentId   父节点id
     * @param merParamId 模板id
     * @param cache      节点缓存
     */
    @Transactional(rollbackFor = Exception.class)
    public void processHierarchy(List<String> row, int level, String parentId, String merParamId, Map<String, String> cache) {
        if (level * 2 - 1 >= row.size() || row.get(level * 2 - 1).isEmpty()) {
            return; // 没有更多层级，停止递归
        }
        String code = row.get(2 * (level - 1));
        String name = row.get(level * 2 - 1);
        Assert.isTrue(StringUtils.isNotBlank(code), "参数编码不能为空");
        Assert.isTrue(StringUtils.isNotBlank(name), "参数名称不能为空");
        if ((2 * level >= row.size()) && Objects.nonNull(cache.get(code))) {
            throw new BusinessException(BaseResponseCodeEnum.CODE_10011, StrUtil.format("参数编码:[{}]重复", code));
        }
        String currentId = cache.computeIfAbsent(code, k -> {
            String paramDetailId = merParamsDetailMapper.getIdByParamCode(merParamId, code);
            if (paramDetailId != null) {
                //判断是否是子节点,是校验编码是否已存在
                if (2 * level < row.size()) {
                    return paramDetailId;
                }
                throw new BusinessException(BaseResponseCodeEnum.CODE_10011, StrUtil.format("参数编码:[{}]已存在", code));
            }
            MerParamsDetail merParamsDetail = new MerParamsDetail();
            merParamsDetail.setParamKey(code);
            merParamsDetail.setParamName(name);
            merParamsDetail.setParentId(parentId);
            merParamsDetail.setLevelNo(level);
            merParamsDetail.setMerParamsId(merParamId);
            merParamsDetailMapper.insert(merParamsDetail);
            return merParamsDetail.getId();
        });
        processHierarchy(row, level + 1, currentId, merParamId, cache); // 递归处理下一层
    }

    private String getCellValue(Row row, int colIndex) {
        Cell cell = row.getCell(colIndex);
        if (cell == null) return "";
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue().trim();
    }


}
